Sql Interview Question

Find the 2nd Highest Salary of Employee?
Method-1

select max (column_name)

from table_name

where column_name not in  (select max(column_name)

                          from table_name);

select  max (Income)

from Employee

where Salary not in  (select Max (Income)

                     from Employee);

select max (Cost)

from price_list

where Salary not in  (select Max (Cost)

                     from price_list);

Method-2

select column_name

from table_name e

where 2 = (select count(distinct column_name)

           from table_name p

           where e.column_name<=p.column_name)

select Income

from Employee e

where 2=(select count(distinct Income)

         from Employee p

         where e.Income<=p.Income)

select Income

from Employee e

where n-1=(select count(distinct Income)

         from Employee p

         where e.Income<=p.Income)

How to compare two files having more than 1 lacks records in hive
select department_id, department_name, count(*)
from (
  select * from tbl1
    union all
  select * from tbl2 ) both
group by department_id, department_name
having count(*) = 1     //if count(*) is 2 -> rows of tbl1,tbl2 are identical.


SELECT D.DEPT_NEM,max(salary)
FROM emptable E INNER JOIN DEPT D
ON E.DEPT_ID=D.DEPT_ID
GROUP BY DEPT_NAME 

Find the even records 
SELECT E.EmpId, E.Project, E.Salary
FROM (
    SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber
    FROM EmployeeSalary
) E
WHERE E.RowNumber % 2 = 0

Find the Odd records 
SELECT E.EmpId, E.Project, E.Salary
FROM (
    SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber
    FROM EmployeeSalary
) E
WHERE E.RowNumber % 2 = 1

Write a SQL query to remove duplicates from a table without using a temporary table.
DELETE FROM EmployeeSalary
WHERE EmpId IN (
SELECT EmpId
FROM EmployeeSalary     
GROUP BY Project, Salary
HAVING COUNT(*) > 1));

Given a table TBL with a field Nmbr that has rows with the following values:

1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1

Write a query to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.

update TBL set Nmbr = case when Nmbr = 0 then Nmbr+2 else Nmbr+3 end;